package dataProces;

import java.sql.ResultSet;

public class DatabaseOperations {
    private final String[] sql = new String[4];
    private DatabaseConnection dConnection = null;
    private boolean state = false;
    FileOperations fOperations = new FileOperations();

    public String dataQueries(String table, String key, String air) {
        dConnection = new DatabaseConnection();
        sql[0] = "use bank_data;";
        int n = key.length();
        String flag;
        if (n == 6)
            flag = ".account='";
        else if (n == 11)
            flag = ".phone='";
        else if (n == 18)
            flag = ".identity_card='";
        else
            return null;
        sql[1] = "select account from user where user" + flag + key + "';";
        try {
            dConnection.mysqlConnection();
            if (!dConnection.mysqlUpdate(sql[0]))
                databaseCreate();
            ResultSet rSet = dConnection.mysqlQuery(sql[1]);
            if (rSet.next()) {
                String account = rSet.getString("account");
                sql[2] = "select " + air + " from " + table + " where account='" + account + "';";
                rSet = dConnection.mysqlQuery(sql[2]);
                if (rSet.next())
                    air = rSet.getString(air);
            } else
                air = null;
        } catch (Exception e) {
            state = false;
            fOperations.logFile("数据查询异常:" + e.getMessage());
        } finally {
            dConnection.mysqlClose();
        }
        return air;
    }

    public void dataUpdates(String table, String account, String air, String val) {
        dConnection = new DatabaseConnection();
        sql[0] = "use bank_data;";
        sql[1] = "update " + table + " set " + air + "='" + val + "' where account='" + account + "';";
        try {
            dConnection.mysqlConnection();
            if (!dConnection.mysqlUpdate(sql[0]))
                databaseCreate();
            state = dConnection.mysqlUpdate(sql[1]);
        } catch (Exception e) {
            state = false;
            fOperations.logFile("数据更新异常:" + e.getMessage());
        } finally {
            dConnection.mysqlClose();
        }
    }

    private String getSql(String str, String[] data) {
        int n = data.length;
        StringBuilder strBuilder = new StringBuilder(str);
        for (int i = 0; i < n; i++) {
            strBuilder.append(data[i]);
            if (i == n - 1)
                strBuilder.append("');");
            else
                strBuilder.append("','");
        }
        return strBuilder.toString();
    }

    public boolean dataInsert(String[] userData, String[] investmentData) {
        dConnection = new DatabaseConnection();
        sql[0] = "use bank_data";
        sql[1] = "insert into investment(account,money,gold,silver,copper,iron) values('";
        sql[1] = getSql(sql[1], investmentData);
        sql[2] = "insert into user(account,identity_card,name,password,phone) values('";
        sql[2] = getSql(sql[2], userData);
        try {
            dConnection.mysqlConnection();
            if (!dConnection.mysqlUpdate(sql[0]))
                databaseCreate();
            for (int i = 1; i < 3; i++) {
                state = dConnection.mysqlUpdate(sql[i]);
            }
        } catch (Exception e) {
            state = false;
            fOperations.logFile("数据插入异常" + e.getMessage());
        } finally {
            dConnection.mysqlClose();
        }
        return state;
    }

    private void databaseCreate() {
        dConnection = new DatabaseConnection();
        sql[0] = "create database bank_data;";
        sql[1] = "use bank_data;";
        sql[2] = "create table investment(account varchar(6) primary key comment '账号',money varchar(20) comment '余额',gold varchar(20) comment '金',silver varchar(20) comment '银',copper varchar(20) comment '铜',iron varchar(20) comment '铁');";
        sql[3] = "create table user(account varchar(6) not null comment '账号',identity_card varchar(18) primary key comment '身份证',name varchar(10) comment '姓名',password varchar(20) comment '密码',phone varchar(11) comment '电话',foreign key(account) references investment(account));";
        try {
            dConnection.mysqlConnection();
            for (int i = 0; i < 4; i++) {
                dConnection.mysqlUpdate(sql[i]);
            }
            state = true;
        } catch (Exception e) {
            state = false;
            fOperations.logFile("表格设计异常:" + e.getMessage());
        } finally {
            dConnection.mysqlClose();
        }
    }

    public void dataDelete(String key) {
        dConnection = new DatabaseConnection();
        sql[0] = "use bank_data;";
        sql[1] = "delete from user where account='" + key + "';";
        sql[2] = "delete from investment where account='" + key + "';";
        try {
            dConnection.mysqlConnection();
            for (int i = 0; i < 3; i++) {
                dConnection.mysqlUpdate(sql[i]);
            }
            state = true;
        } catch (Exception e) {
            state = false;
            fOperations.logFile("数据删除异常:" + e.getMessage());
        } finally {
            dConnection.mysqlClose();
        }
    }
}
